﻿using DataModels;
using DataModels.Enumerations;
using DataModels.Models;
using DataModels.Models.Context;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;

namespace GenericTools.CustomReporting
{
    public class ReportQuerying : GenericTools.Models.BaseWebAuthorization
    {
        #region Constants
        public const int STATUS_OK_TO_CONTINUE = 0;
        public const int STATUS_NAME_IS_MISSING = 1;
        public const int STATUS_NAME_ALREADY_EXISTS = 2;
        public const int STATUS_DEFINITION_IS_MISSING = 3;
        public const int STATUS_OBJECT_DOES_NOT_EXISTS = 4;
        public const int STATUS_USER_ID_IS_INVALID = 5;
        public const int STATUS_OBJECT_HAS_RELATED_CHILDREN = 6;

        public const int DEFAULT_COMMAND_TIMEOUT = 30;
        #endregion

        private static IQServices.IQService webService;

        private static int _CommandTimeout;
        private static string _QueryingConnectionString;

        /// <summary>
        /// The time in seconds to wait for the command to execute. The default is 30 seconds.
        /// A value of 0 indications no limit, and should be avoided in a CommandTimeout because
        /// an attempt to execute a command will wait indefinitely.
        /// </summary>
        public static int CommandTimeout
        {
            get { return _CommandTimeout; }
            set { _CommandTimeout = value; }
        }

        /// <summary>
        /// Connection of the database when querying for reports
        /// </summary>
        public static string QueryingConnectionString
        {
            get { return _QueryingConnectionString; }
            set { _QueryingConnectionString = value; }
        }

        static ReportQuerying()
        {
            webService = new IQServices.IQService();
            webService.AuthenticationHeaderValue = new IQServices.AuthenticationHeader();
            webService.Url = AppConfiguration.WebServiceURL;
            _CommandTimeout = DEFAULT_COMMAND_TIMEOUT;
        }

        #region Instance Methods Wrapping for UI
        public List<QueryType> GetReportQueryTypes()
        {
            return ReportQuerying.GetQueryTypes();
        }

        public List<QueryCategory> GetAllReportQueryCategories()
        {
            return ReportQuerying.GetAllQueryCategories();
        }

        public DataTable GetAllExpectedReports()
        {
            return Utilities.GetAllExpectedReports();
        }
        #endregion

        public static User GetUserProfile(int userId)
        {
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new CustomReportsContext())
                    {
                        db.Configuration.ProxyCreationEnabled = false;
                        var user = (from u in db.Users
                                    where u.ID == userId
                                    select u).FirstOrDefault();
                        if (user == null) return null;
                        else return user;
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    webService.AuthenticationHeaderValue.Username = Username;
                    webService.AuthenticationHeaderValue.PasswordHash = PasswordHash;

                    var result = webService.GetUserProfile(userId);
                    var output = Services.Deserialize(result, typeof(User)) as User;
                    return output;
            }

            return null;
        }

        public static List<QueryCategory> GetAllQueryCategories()
        {
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new CustomReportsContext())
                    {
                        db.Configuration.ProxyCreationEnabled = false;
                        var results = from c in db.QueryCategories
                                          .Include("Queries")
                                          .Include("Queries.QueryParameters")
                                          .Include("Queries.QueryType")
                                      //&& c.Queries.Exists(q => q.Deleted == false)
                                      select c;
                        return results.ToList();
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    webService.AuthenticationHeaderValue.Username = Username;
                    webService.AuthenticationHeaderValue.PasswordHash = PasswordHash;
                    
                    var result = webService.GetAllQueryCategories();
                    var output = new List<QueryCategory>();
                    foreach (var i in result)
                    {
                        var item = Services.Deserialize(i, typeof(QueryCategory)) as QueryCategory;
                        output.Add(item);
                    }
                    return output;
            }

            return null;
        }

        public static List<Query> GetAllQueries()
        {
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new CustomReportsContext())
                    {
                        db.Configuration.ProxyCreationEnabled = false;
                        var results = from c in db.Queries.Include("QueryParameters")
                                      select c;
                        return results.ToList();
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    webService.AuthenticationHeaderValue.Username = Username;
                    webService.AuthenticationHeaderValue.PasswordHash = PasswordHash;

                    var result = webService.GetAllQueries();
                    var output = new List<Query>();
                    foreach (var i in result)
                    {
                        var item = Services.Deserialize(i, typeof(Query)) as Query;
                        output.Add(item);
                    }
                    return output;
            }

            return null;
        }

        public static List<QueryType> GetQueryTypes()
        {
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new CustomReportsContext())
                    {
                        db.Configuration.ProxyCreationEnabled = false;
                        var results = from c in db.QueryTypes
                                      select c;
                        return results.ToList();
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    var result = webService.GetQueryTypes();
                    var output = new List<QueryType>();
                    foreach (var i in result)
                    {
                        var item = Services.Deserialize(i, typeof(QueryType)) as QueryType;
                        output.Add(item);
                    }
                    return output;
            }

            return null;
        }

        public static int AddQueryCategory(QueryCategory queryCategory)
        {
            if (string.IsNullOrWhiteSpace(queryCategory.Name)) return STATUS_NAME_IS_MISSING;

            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new CustomReportsContext())
                    {
                        // If this category exists, then don't save it
                        var cat = (from c in db.QueryCategories
                                   where c.Name == queryCategory.Name
                                   select c).FirstOrDefault();
                        if (cat != null) return STATUS_NAME_ALREADY_EXISTS;
                        else
                        {
                            queryCategory.CreateDate = DateTime.Now;
                            db.QueryCategories.Add(queryCategory);
                            db.SaveChanges();
                            return STATUS_OK_TO_CONTINUE;
                        }
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:                    
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    webService.AuthenticationHeaderValue.Username = Username;
                    webService.AuthenticationHeaderValue.PasswordHash = PasswordHash;

                    var category = new IQServices.QueryCategory
                    {
                        Name = queryCategory.Name,
                        Description = queryCategory.Description,
                        CreateDate = queryCategory.CreateDate,
                        CreatedByUserId = queryCategory.CreatedByUserId
                    };
                    var status = webService.AddQueryCategory(category);
                    return status;
            }

            return STATUS_OK_TO_CONTINUE;
        }

        public static int UpdateQueryCategory(QueryCategory queryCategory)
        {
            if (string.IsNullOrWhiteSpace(queryCategory.Name)) return STATUS_NAME_IS_MISSING;

            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new CustomReportsContext())
                    {
                        // If this category does not exists, then ignore these changes
                        var target = (from c in db.QueryCategories
                                      where c.Id == queryCategory.Id
                                      select c).FirstOrDefault();
                        if (target == null) return STATUS_OBJECT_DOES_NOT_EXISTS;
                        else
                        {
                            // The target name should be unique in case of changing it, otherwise ignore these changes
                            if (!target.Name.Equals(queryCategory.Name, StringComparison.OrdinalIgnoreCase))
                            {
                                var cat = (from c in db.QueryCategories
                                           where c.Name == queryCategory.Name
                                           select c).FirstOrDefault();
                                if (cat != null) return STATUS_NAME_ALREADY_EXISTS;
                            }

                            target.Name = queryCategory.Name;
                            target.Description = queryCategory.Description;
                            target.UpdateDate = DateTime.Now;
                            target.UpdatedByUserId = queryCategory.UpdatedByUserId;
                            db.SaveChanges();
                            return STATUS_OK_TO_CONTINUE;                          
                        }
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    webService.AuthenticationHeaderValue.Username = Username;
                    webService.AuthenticationHeaderValue.PasswordHash = PasswordHash;

                    var category = new IQServices.QueryCategory
                    {
                        Id = queryCategory.Id,
                        Name = queryCategory.Name,
                        Description = queryCategory.Description,
                        UpdatedByUserId = queryCategory.UpdatedByUserId
                    };
                    var status = webService.UpdateQueryCategory(category);
                    return status;
            }

            return STATUS_OK_TO_CONTINUE;
        }

        public static int DeleteQueryCategory(int queryCategoryId)
        {
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new CustomReportsContext())
                    {
                        // If this category does not exists, then ignore these changes
                        var target = (from c in db.QueryCategories
                                      where c.Id == queryCategoryId                                     
                                      select c).FirstOrDefault();
                        if (target == null) return STATUS_OBJECT_DOES_NOT_EXISTS;
                        else
                        {
                            // In order to delete the query category, that category must be completely
                            // isolated. This means it should not have any related queries as in the 
                            // case of categorization.
                            if (target.Queries.Count > 0) return STATUS_OBJECT_HAS_RELATED_CHILDREN;
                            else
                            {
                                db.QueryCategories.Remove(target);
                                db.SaveChanges();
                                return STATUS_OK_TO_CONTINUE;
                            }
                        }
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    webService.AuthenticationHeaderValue.Username = Username;
                    webService.AuthenticationHeaderValue.PasswordHash = PasswordHash;
                    var status = webService.DeleteQueryCategory(queryCategoryId);
                    return status;
            }

            return STATUS_OK_TO_CONTINUE;
        }

        public static int AddQuery(Query query, List<QueryParameter> parameters)
        {
            if (string.IsNullOrWhiteSpace(query.Name)) return STATUS_NAME_IS_MISSING;

            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new CustomReportsContext())
                    {
                        // If this category exists, then don't save it
                        var cat = (from c in db.Queries
                                   where c.Name == query.Name
                                   select c).FirstOrDefault();
                        if (cat != null) return STATUS_NAME_ALREADY_EXISTS;
                        else
                        {
                            query.CreateDate = DateTime.Now;
                            db.Queries.Add(query);
                            db.SaveChanges();

                            // Delete all related query parameters and update with the new ones
                            var parems = from p in db.QueryParameters
                                         where p.QueryId == query.Id
                                         select p;
                            foreach (var p in parems) db.QueryParameters.Remove(p);

                            // Now adding new parameters to this query.
                            if (parameters != null)
                            {
                                foreach (var p in parameters) p.QueryId = query.Id;
                                db.QueryParameters.AddRange(parameters);
                            }
                            db.SaveChanges();
                            return STATUS_OK_TO_CONTINUE;
                        }
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    webService.AuthenticationHeaderValue.Username = Username;
                    webService.AuthenticationHeaderValue.PasswordHash = PasswordHash;

                    var category = new IQServices.Query
                    {
                        Name = query.Name,
                        Description = query.Description,
                        InternalName = query.InternalName,
                        Definition = query.Definition,
                        More = query.More,
                        QueryTypeId = query.QueryTypeId,
                        CreateDate = query.CreateDate,
                        CreatedByUserId = query.CreatedByUserId
                    };

                    var param = new IQServices.QueryParameter[parameters.Count];
                    for (int i = 0; i < parameters.Count; i++)
                    {
                        var parameter = parameters[i];
                        var p = new IQServices.QueryParameter
                        {
                            Name = parameter.Name,
                            Description = parameter.Description,
                            ValidationExpression = parameter.ValidationExpression,
                            DataTypeName = parameter.DataTypeName,
                            QueryId = parameter.QueryId
                        };
                        param[i] = p;
                    }
                    var status = webService.AddQuery(category, param);
                    return status;
            }

            return STATUS_OK_TO_CONTINUE;
        }

        public static int UpdateQuery(Query query, List<QueryParameter> parameters)
        {
            if (string.IsNullOrWhiteSpace(query.Name)) return STATUS_NAME_IS_MISSING;

            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new CustomReportsContext())
                    {
                        // If this category does not exists, then ignore these changes
                        var target = (from c in db.Queries
                                      where c.Id == query.Id
                                      select c).FirstOrDefault();
                        if (target == null) return STATUS_OBJECT_DOES_NOT_EXISTS;
                        else
                        {
                            // The target name should be unique in case of changing it, otherwise ignore these changes
                            if (!target.Name.Equals(query.Name, StringComparison.OrdinalIgnoreCase))
                            {
                                var cat = (from c in db.Queries
                                           where c.Name == query.Name
                                           select c).FirstOrDefault();
                                if (cat != null) return STATUS_NAME_ALREADY_EXISTS;
                            }

                            target.Name = query.Name;
                            target.Description = query.Description;
                            target.Definition = query.Definition;
                            target.More = query.More;
                            target.QueryTypeId = query.QueryTypeId;
                            target.UpdateDate = DateTime.Now;
                            target.UpdatedByUserId = query.UpdatedByUserId;
                            db.SaveChanges();

                            // Delete all related query parameters and update with the new ones
                            var parems = from p in db.QueryParameters
                                         where p.QueryId == query.Id
                                         select p;
                            foreach (var p in parems) db.QueryParameters.Remove(p);

                            // Now adding new parameters to this query.
                            if (parameters != null)
                            {
                                foreach (var p in parameters) p.QueryId = target.Id;
                                db.QueryParameters.AddRange(parameters);
                            }
                            db.SaveChanges();
                            return STATUS_OK_TO_CONTINUE;
                        }
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    webService.AuthenticationHeaderValue.Username = Username;
                    webService.AuthenticationHeaderValue.PasswordHash = PasswordHash;

                    var category = new IQServices.Query
                    {
                        Id = query.Id,
                        Name = query.Name,
                        Description = query.Description,
                        Definition = query.Definition,
                        More = query.More,
                        QueryTypeId = query.QueryTypeId,
                        UpdatedByUserId = query.UpdatedByUserId
                    };

                    var param = new IQServices.QueryParameter[parameters.Count];
                    for (int i = 0; i < parameters.Count; i++)
                    {
                        var parameter = parameters[i];
                        var p = new IQServices.QueryParameter
                        {
                            Name = parameter.Name,
                            Description = parameter.Description,
                            ValidationExpression = parameter.ValidationExpression,
                            DataTypeName = parameter.DataTypeName,
                            QueryId = parameter.QueryId
                        };
                        param[i] = p;
                    }
                    var status = webService.UpdateQuery(category, param);
                    return status;
            }

            return STATUS_OK_TO_CONTINUE;
        }

        public static int DeleteQuery(Query query)
        {
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new CustomReportsContext())
                    {
                        // If this category does not exists, then ignore these changes
                        var target = (from c in db.Queries
                                      where c.Id == query.Id
                                      select c).FirstOrDefault();
                        if (target == null) return STATUS_OBJECT_DOES_NOT_EXISTS;
                        else
                        {
                            // In order to delete the query category, that category must be completely
                            // isolated. This means it should not have any related queries as in the 
                            // case of categorization.
                            if (target.QueryCategories.Count() > 0) return STATUS_OBJECT_HAS_RELATED_CHILDREN;
                            else
                            {
                                target.DeleteDate = DateTime.Now;
                                target.Deleted = true;
                                target.DeletedByUserId = query.DeletedByUserId;
                                db.SaveChanges();
                                return STATUS_OK_TO_CONTINUE;
                            }
                        }
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    webService.AuthenticationHeaderValue.Username = Username;
                    webService.AuthenticationHeaderValue.PasswordHash = PasswordHash;

                    var qry = new IQServices.Query
                    {
                        Id = query.Id,
                        DeletedByUserId = query.DeletedByUserId
                    };
                    var status = webService.DeleteQuery(qry);
                    return status;
            }

            return STATUS_OK_TO_CONTINUE;
        }

        public static int AddQueryIntoCategories(int queryId, List<int> queryCategories, bool keepExistingCategories)
        {
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new CustomReportsContext())
                    {
                        var targetQuery = (from q in db.Queries
                                           where q.Id == queryId
                                           select q).FirstOrDefault();
                        if (targetQuery == null) return STATUS_OBJECT_DOES_NOT_EXISTS;
                        else
                        {
                            if (!keepExistingCategories) targetQuery.QueryCategories.Clear();
                            db.SaveChanges();
                            
                            var categories = from c in db.QueryCategories
                                             where queryCategories.Contains(c.Id)
                                             select c;
                            var submittedCategories = categories.ToList();

                            if (keepExistingCategories)
                            {
                                // From the submitted list of categories, remove those which already exists
                                // in the target query object, so that when we add, we'll only add the new 
                                // categories.                                
                                foreach (var c in targetQuery.QueryCategories)
                                {
                                    foreach (var sc in submittedCategories)
                                    {
                                        if (sc.Id == c.Id)
                                        {
                                            submittedCategories.Remove(sc);
                                            break;
                                        }
                                    }
                                }
                            }
                            
                            targetQuery.QueryCategories.AddRange(submittedCategories);
                            db.SaveChanges();
                            return STATUS_OK_TO_CONTINUE;
                        }                        
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    webService.AuthenticationHeaderValue.Username = Username;
                    webService.AuthenticationHeaderValue.PasswordHash = PasswordHash;

                    int[] queryCategoryIds = queryCategories.ToArray();
                    int status = webService.AddQueryIntoCategories(queryId, queryCategoryIds, keepExistingCategories);
                    return status;                    
            }

            return STATUS_OK_TO_CONTINUE;
        }


        private static bool queryRunningCompleted = false;
        private static DataTable queryRunningResults = null;
        private static Exception queryRunningException = null;
        private const int MINIMUM_QUERY_WAITING_TIME = 3000;
        public static DataTable RunQuery(Query query, List<string> parameterValues)
        {
            if (query.QueryParameters == null) throw new ArgumentException("Query parameters property is not set");
            if (parameterValues == null) throw new ArgumentNullException("parameterValues can not be nul");
            if (query.QueryParameters.Count != parameterValues.Count) throw new ConstraintException("Number of query parameters should be the same as the list of supplied value");

            DataTable dataTable = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    string rogueWord;
                    bool allowed = IsThisQueryPermissible(query.Definition, out rogueWord);
                    if (!allowed)
                    {
                        string message = string.Format("'{0}' is not permitted in the expression or command", rogueWord);
                        throw new Exception(message);
                    }

                    dataTable = ExecuteQuery(query, parameterValues);                    
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    webService.AuthenticationHeaderValue.Username = Username;
                    webService.AuthenticationHeaderValue.PasswordHash = PasswordHash;

                    var queryParameters = new IQServices.QueryParameter[query.QueryParameters.Count];
                    for (int i = 0; i < query.QueryParameters.Count; i++)
                    {
                        var p = query.QueryParameters[i];
                        var param = new IQServices.QueryParameter
                        {
                            Name = p.Name,
                            Description = p.Description,
                            QueryId = p.QueryId,
                            DataTypeName = p.DataTypeName,
                            ValidationExpression = p.ValidationExpression
                        };
                        queryParameters[i] = param;
                    }

                    var targetQuery = new IQServices.Query
                    {
                        Id = query.Id,
                        Name = query.Name,
                        Description = query.Description,
                        InternalName = query.InternalName,
                        More = query.More,
                        Definition = query.Definition,
                        QueryParameters = queryParameters
                    };

                    //string output = webService.RunQuery(targetQuery, parameterValues.ToArray());
                    queryRunningCompleted = false;
                    webService.Timeout = System.Threading.Timeout.Infinite; //Run indefinitely
                    webService.RunQueryCompleted += webService_RunQueryCompleted;
                    webService.RunQueryAsync(targetQuery, parameterValues.ToArray());

                    // We always wait for at least 3 seconds          
                    do
                    {
                        Thread.Sleep(MINIMUM_QUERY_WAITING_TIME);
                    }
                    while (!queryRunningCompleted);

                    if (queryRunningException != null) throw queryRunningException;
                    else dataTable = queryRunningResults;
                    //dataTable = DataTransformation.Deserialize(output);
                    break;
            }

            return dataTable;
        }

        static void webService_RunQueryCompleted(object sender, IQServices.RunQueryCompletedEventArgs e)
        {
            // Pass on any error that may arise
            if (e.Error == null)
            {
                string output = e.Result;
                queryRunningResults = DataTransformation.Deserialize(output);
                queryRunningException = null;
            }
            else
            {
                queryRunningException = e.Error;
                queryRunningResults = null;
            }
            
            queryRunningCompleted = true;
        }

        private static bool IsThisQueryPermissible(string definition, out string rejectWord)
        {
            rejectWord = string.Empty;
            if (string.IsNullOrWhiteSpace(definition)) return false;

            string[] forbiddenWords = new string[]
            {                
                "DROP", "CREATE", "ALTER", "UPDATE", "CRETE VIEW",
                "CREATE TABLE", "INTO", "CREATE FUNCTION", "CREATE PROCEDURE",
                "RESTORE DATABASE", "BACKUP DATABASE", "TRUNCATE", "DATABASE",
                "PROCEDURE", "TRIGGER", "FUNCTION", "SYNONYM"
            };

            Regex trimmer = new Regex(@"\s\s+", RegexOptions.Compiled);
            definition = trimmer.Replace(definition, " ");
            definition = definition.ToUpper();

            foreach (string word in forbiddenWords)
            {
                if (definition.Contains(word))
                {
                    // if the next word after INTO contains declared variable, in this
                    // it's possibly the in-memory table. And this declared variable must 
                    // be the next word after INTO
                    if (word == "INTO")
                    {
                        string[] parts = definition.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
                        bool okToContinue = false;
                        for (int i = 0; i < parts.Length; i++)
                        {
                            if (parts[i] == word)
                            {
                                // Now the next word should start with @
                                if ((i + 2) <= parts.Length) 
                                {
                                    string nextWord = parts[i + 1];
                                    if (nextWord.StartsWith("@"))
                                    {
                                        okToContinue = true;
                                        break;
                                    }
                                }
                            }
                        }

                        if (okToContinue) continue;
                    }
                    rejectWord = word;
                    return false;
                }
            }
            return true;
        }

        private static DataTable ExecuteQuery(Query query, List<string> parameterValues)
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DbDataReader reader = null;

            try
            {
                connection = new SqlConnection(QueryingConnectionString);
                connection.Open();
                command = connection.CreateCommand();
                command.CommandText = query.Definition;
                command.CommandTimeout = CommandTimeout;

                // Now adding parameters
                for (int i = 0; i < query.QueryParameters.Count; i++)
                {
                    var param = query.QueryParameters[i];
                    DbParameter parameter = command.CreateParameter();
                    parameter.ParameterName = param.Name;
                    parameter.Value = parameterValues[i];
                    command.Parameters.Add(parameter);
                }

                reader = command.ExecuteReader();
                DataTable dataTable = new DataTable();
                dataTable.Load(reader);               

                return dataTable;
            }
            finally
            {
                if (connection != null)
                {
                    if (command != null)
                    {
                        if (reader != null) reader.Dispose();
                        command.Dispose();
                    }
                    connection.Dispose();
                }
            }
        }
    }
}
